/*
 * MySQLDbIf.java
 *
 * Created on July 8, 2006, 4:02 PM
 *
 */

package org.vcboard.database;

import java.sql.*;
import java.util.*;
import javax.swing.*;
import org.apache.log4j.*;
import org.vcboard.*;
import org.vcboard.model.*;

/**
 * @author Josh
 */
public class MySQLDbIf
      extends DbIf
{
   static Logger log = Logger.getLogger(MySQLDbIf.class);
   static MySQLDbIf mysql = null;
   
   /** Creates a new instance of MySQLDbIf */
   public MySQLDbIf()
   {
   }
      
   public static MySQLDbIf getInstance()
   {
      if (mysql == null)
      {
         mysql = new MySQLDbIf();
      }
      
      return mysql;
   }
   
   public User logIn(String username, String password)
   {
      PreparedStatement ps = null;
      ResultSet rs = null;
      String sql = "SELECT * FROM `users` WHERE `username`=? AND `password`=? LIMIT 1";
      User user = new User();
      
      try
      {
         log.info("Attempting login for " + username + " with password " + password);
         
         if (getMySQLConn() != null)
         {
            ps = getMySQLConn().prepareStatement(sql);
            ps.setString(1, username);
            ps.setString(2, password);
            rs = ps.executeQuery();

            if (rs.next())
            {
               log.info("Login successful");
               user = assembleUser(rs);
            }
            else
            {
               log.error("Login failed");
            }
         }
         else
         {
            log.error("No connection to database...");
            JOptionPane.showMessageDialog(null,
                  "Could not connect to database.  Please check your " +
                  "connection properties.",
                  "Error",
                  JOptionPane.ERROR_MESSAGE);
         }
      }
      catch (SQLException e)
      {
         log.error("Failed to login: " + e);
      }
      finally
      {
         close(ps);
         close(rs);
      }
      
      return user;
   }
   
   private User assembleUser(ResultSet rs)
   {
      User tmp = null;
      
      try
      {
         tmp = new User();
         tmp.setUserId(rs.getInt("id"));
         tmp.setUsername(rs.getString("username"));
         tmp.setPassword(rs.getString("password"));
         tmp.setPeriod(rs.getLong("period"));
         tmp.setStart(rs.getTimestamp("started"));
         tmp.setTimeout(rs.getLong("timeout"));
         tmp.setTimeoutEnabled(rs.getLong("timeout_enabled") == 1 ? true : false);
         tmp.setOffset(rs.getInt("server_offset"));
      }
      catch (SQLException e)
      {
         log.error("Failed to assemble user: " + e);
      }
      
      return tmp;
   }
   
   public boolean isClockedIn()
   {
      PreparedStatement st = null;
      ResultSet rs = null;
      String sql = "SELECT * FROM `logs` WHERE `logout_time` IS NULL AND `user_id`=?";
      boolean retVal = false;
      
      try
      {
         st = getMySQLConn().prepareStatement(sql);
         st.setInt(1, WorkTimer.getUser().getUserId());
         rs = st.executeQuery();
         
         if (rs.next())
         {
            //log.info("User is clocked in");
            retVal = true;
         }
      }
      catch (Exception e)
      {
         log.error("Failed to determine if user is clocked in: " + e);
      }
      finally
      {
         close(st);
         close(rs);
      }
      
      WorkTimer.fireLogsChanged();
      return retVal;
   }
   
   public void toggleStatus()
   {
      if (WorkTimer.getUser().getUserId() != -1)
      {
         if (isClockedIn())
         {
            clockOut();
         }
         else
         {
            clockIn();
         }
      }
   }
   
   public boolean clockIn()
   {
      PreparedStatement st = null;
      String sql = "INSERT INTO `logs` (`user_id`, `login_time`) VALUES (?, NOW())";
      boolean retVal = false;
      
      try
      {
         //log.info("UserID: " + WorkTimer.getUser().getUserId());
         
         if (WorkTimer.getUser().getUserId() != -1)
         {
            if (!isClockedIn())
            {
               st = getMySQLConn().prepareStatement(sql);
               st.setInt(1, WorkTimer.getUser().getUserId());
               retVal = st.execute();
               
               if (retVal)
               {
                  log.info("Clock in successful");
               }
            }
         }
         else
         {
            log.error("Cannot clock in (not logged in)");
            JOptionPane.showMessageDialog(null,
                  "You must successfully login before you can clock in.",
                  "Clock In Failed",
                  JOptionPane.ERROR_MESSAGE);
         }
      }
      catch (Exception e)
      {
         log.error("Failed to log in: " + e);
      }
      finally
      {
         close(st);
      }
      
      WorkTimer.fireLogsChanged();
      return retVal;
   }
   
   public boolean clockOut()
   {
      PreparedStatement st = null;
      String sql = "UPDATE `logs` SET `logout_time`=NOW() WHERE `logout_time` IS NULL AND `user_id`=?";
      boolean retVal = false;
      
      try
      {
         //log.info("UserID: " + WorkTimer.getUser().getUserId());
         
         if (WorkTimer.getUser().getUserId() != -1)
         {
            if (isClockedIn())
            {
               st = getMySQLConn().prepareStatement(sql);
               st.setInt(1, WorkTimer.getUser().getUserId());
               retVal = st.execute();
               
               if (retVal)
               {
                  log.info("Clock out successful");
               }
            }
         }
         else
         {
            log.error("Cannot clock out (not logged in)");
            JOptionPane.showMessageDialog(null,
                  "You must successfully login before you can clock out.",
                  "Clock Out Failed",
                  JOptionPane.ERROR_MESSAGE);
         }
      }
      catch (Exception e)
      {
         log.error("Failed to log out: " + e);
      }
      finally
      {
         close(st);
      }
      
      WorkTimer.fireLogsChanged();
      return retVal;
   }
   
   public boolean updateTimeout(boolean isEnabled, String timeout)
   {
      boolean retVal = false;
      PreparedStatement ps = null;
      String sql = "UPDATE `users` SET `timeout_enabled`=?, `timeout`=? WHERE `id`=? LIMIT 1";
      
      try
      {
         ps = getMySQLConn().prepareStatement(sql);
         ps.setBoolean(1, isEnabled);
         ps.setInt(2, Integer.parseInt(timeout) * 60);
         ps.setInt(3, WorkTimer.getUser().getUserId());
         retVal = ps.execute();
      }
      catch (Exception e)
      {
         log.error("Failed to update timeout information");
      }
      finally
      {
         close(ps);
      }
      
      return retVal;
   }
   
   public boolean updateOffset(String offset)
   {
      boolean retVal = false;
      PreparedStatement ps = null;
      String sql = "UPDATE `users` SET `server_offset`=? WHERE `id`=? LIMIT 1";
      
      try
      {
         ps = getMySQLConn().prepareStatement(sql);
         ps.setInt(1, Integer.parseInt(offset));
         ps.setInt(2, WorkTimer.getUser().getUserId());
         retVal = ps.execute();
      }
      catch (Exception e)
      {
         log.error("Failed to update server offset information");
      }
      finally
      {
         close(ps);
      }
      
      return retVal;
   }
   
   public boolean updateLog(TimeLog inLog)
   {
      PreparedStatement ps = null;
      String sql = "UPDATE `logs` SET `login_time`=?, `logout_time`=? WHERE `user_id`=? AND `id`=?";
      boolean retVal = false;
      
      try
      {  
         ps = getMySQLConn().prepareStatement(sql);
         ps.setTimestamp(1, new Timestamp(inLog.getClockInTimeMillis()));
         ps.setTimestamp(2, new Timestamp(inLog.getClockOutTimeMillis()));
         ps.setInt(3, WorkTimer.getUser().getUserId());
         ps.setInt(4, inLog.getLogId());
         
         retVal = ps.execute();
      }
      catch (Exception e)
      {
         log.error("Failed to update log " + inLog.getLogId() + ": " + e);
      }
      finally
      {
         close(ps);
      }
      
      WorkTimer.fireLogsChanged();
      return retVal;
   }
   
   public Vector<TimeLog> getLogs()
   {
      PreparedStatement ps = null;
      ResultSet rs = null;
      Vector<TimeLog> allLogs = new Vector<TimeLog>();
      Map multiEntry = new HashMap();
      TimeLog tLog = new TimeLog();
      TimeLog lastDay = new TimeLog();
      
      LogDate start = null;
      LogDate end = null;
      
      if (WorkTimer.getUser().getUserId() != -1)
      {
         //log.info("Retrieving logs for " + WorkTimer.getUser().getUserId());
         
         if (WorkTimer.getLogPeriod() == null)
         {
            start = new LogDate(WorkTimer.getUser().getStart());
         }
         else
         {
            start = new LogDate(WorkTimer.getLogPeriod());
         }
         end = new LogDate(start.getTimeInSeconds() + WorkTimer.getUser().getPeriod());
         
         //log.info("Start: " + start.getTimeInSeconds() + " Period: " + WorkTimer.getUser().getPeriod());
         //log.info("Getting logs between " + start.getTimestamp() + " & " + end.getTimestamp());
         
         try
         {
            ps = getMySQLConn().prepareStatement("SELECT * FROM `logs` WHERE `user_id`=? AND `login_time` > ? AND `login_time` < ? ORDER BY `login_time` DESC");
            ps.setInt(1, WorkTimer.getUser().getUserId());
            ps.setTimestamp(2, start.getTimestamp());
            ps.setTimestamp(3, end.getTimestamp());
            rs = ps.executeQuery();

            while (rs.next())
            {
               tLog = assemleTimeLog(rs);
               
               
               /*
                * TODO: day total row
                * 
                
               if (lastDay.getClockIn() == null && !lastDay.isTotal())
               {
                  log.info("setting default lastDay");
                  lastDay = tLog;
               }
               
               if (!lastDay.getClockIn().getDate().equals(tLog.getClockIn().getDate()))
               {
                  log.info(lastDay.getClockIn().getDate() + " != " + tLog.getClockIn().getDate());
                  lastDay = tLog;
                  lastDay.setLogId(-1);
                  lastDay.setTotal(true);
                  allLogs.add(lastDay);
                  
                  lastDay = new TimeLog();
                  lastDay.setLogId(-1);
                  lastDay.setTotal(true);
                  lastDay = tLog;
               }
               int val = multiEntry.get(tLog.getClockIn().getDate()) != null ? 
                  Integer.parseInt(multiEntry.get(tLog.getClockIn().getDate()).toString()) : 
                  0;
               multiEntry.put(tLog.getClockIn().getDate(), val + 1);
               
               if (lastDay == null)
               {
                  log.info("bla: " + tLog.getClockIn());
                  lastDay = tLog;
                  log.info("wla: " + lastDay.getClockIn());
               }
               
               if (!tLog.getDate().equals(lastDay.getDate()))
               {
                  lastDay.setLogId(-1);
                  lastDay.setTotal(true);
                  log.info("Boo: " + lastDay.getClockIn());
                  allLogs.add(lastDay);
                  lastDay = tLog;
                  log.info("Woo: " + lastDay.getClockIn());
               }
               else
               {
                  lastDay.setClockIn(new LogDate(rs.getLong("login_time")));
               }
                */
               
               allLogs.add(tLog);
            }
            
            Iterator it = multiEntry.keySet().iterator();
            while (it.hasNext())
            {
               String key = it.next().toString();
               log.info(key + " -- " + multiEntry.get(key));
            }
         }
         catch (Exception e)
         {
            log.error("Failed to retrieve logs: " + e);
         }
         finally
         {
            close(ps);
            close(rs);
         }
      }
      
      //log.info("Finished retrieving logs");
      //WorkTimer.fireLogsChanged();
      return allLogs;
   }
   
   private TimeLog assemleTimeLog(ResultSet rs)
   {
      TimeLog tmp = new TimeLog();
      
      try
      {
         tmp.setLogId(rs.getInt("id"));
         tmp.setClockIn(new LogDate(rs.getTimestamp("login_time")));

         if (rs.getDate("logout_time") != null)
         {
            tmp.setLogout(new LogDate(rs.getTimestamp("logout_time")));
         }
      }
      catch (Exception e)
      {
         log.error("Failed to assemble timelog: " + e);
      }
      
      return tmp;
   }
   
   public boolean setPeriod(LogDate begin, long freq)
   {
      boolean retVal = false;
      
      //log.info(begin.getTimestamp());
      //log.info(freq * 86400);
      
      PreparedStatement ps = null;
      String sql = "UPDATE `users` SET `period`=?, `started`=? WHERE `id`=?";
      
      try
      {
         ps = getMySQLConn().prepareStatement(sql);
         ps.setLong(1, freq * 86400);
         ps.setTimestamp(2, begin.getTimestamp());
         ps.setInt(3, WorkTimer.getUser().getUserId());
         retVal = ps.execute();
      }
      catch (Exception e)
      {
         log.error("Failed to update period: " + e);
      }
      finally
      {
         close(ps);
      }
      
      return retVal;
   }
   
   public boolean installTables()
   {
      boolean retVal = true;
      int tmp = -1;
      Statement st = null;
      Vector<String> sql = new Vector<String>();
      
      sql.add("DROP TABLE IF EXISTS `logs`");
      sql.add("CREATE TABLE `logs` (" +
            "`id` int(10) NOT NULL auto_increment," +
            "`user_id` int(20) NOT NULL default '0'," +
            "`login_time` timestamp NOT NULL," +
            "`logout_time` timestamp NULL default NULL," +
            "PRIMARY KEY  (`id`))");
      sql.add("DROP TABLE IF EXISTS `users`");
      sql.add("CREATE TABLE `users` (" +
            "`id` int(20) NOT NULL auto_increment," +
            "`username` varchar(40) NOT NULL default ''," +
            "`password` varchar(35) NOT NULL default ''," +
            "`period` varchar(10) NOT NULL default '604800' COMMENT 'Pay period'," +
            "`started` timestamp NULL default NULL COMMENT 'Date/Time when project began'," +
            "`timeout` int(7) default '600' COMMENT 'Seconds to wait before automatically logging user off'," +
            "`timeout_enabled` tinyint(1) default '1'," +
            "`server_offset` int(3) default 0 COMMENT 'User time offset from server (+/-)'," +
            "PRIMARY KEY  (`id`))");
      
      try
      {
         for (String q : sql)
         {
            st = getMySQLConn().createStatement();
            //log.info("Executing query: " + q);
            tmp = st.executeUpdate(q);
            
            if (retVal && tmp != 0)
            {
               retVal = false;
               
               log.error("Failed to execute query: " + q);
               JOptionPane.showMessageDialog(null,
                     "The tables could not be installed successfully.  Please " +
                     "try again.",
                     "Error",
                     JOptionPane.ERROR_MESSAGE);
            }
         }
      }
      catch (SQLException e)
      {
         log.error("Failed to install tables: " + e);
      }
      finally
      {
         close(st);
      }
      
      return retVal;
   }
   
   public boolean tablesExist()
   {
      log.info("Checking for existing tables");
      
      boolean retVal = false;
      Statement st = null;
      ResultSet rs = null;
      
      try
      {
         st = getMySQLConn().createStatement();
         rs = st.executeQuery("SELECT * FROM `logs`");
         
         if (rs.next())
         {
            st = getMySQLConn().createStatement();
            rs = st.executeQuery("SELECT * FROM `users`");
            
            if (rs.next())
            {
               log.info("Tables exist already");
               retVal = true;
            }
         }
      }
      catch (SQLException e)
      {
         log.error("Failed to check for tables: " + e);
      }
      finally
      {
         try
         {
            close(st);
            close(rs);
         }
         catch (Exception e)
         {
            log.error("Error while closing variables: " + e);
         }
      }
      
      return retVal;
   }
   
   public boolean userExists(String username)
   {
      log.info("Checking for existing user '" + username + "'");
      
      boolean retVal = false;
      PreparedStatement ps = null;
      ResultSet rs = null;
      String sql = "SELECT * FROM users WHERE username=? LIMIT 1";
      
      try
      {
         ps = getMySQLConn().prepareStatement(sql);
         ps.setString(1, username);
         rs = ps.executeQuery();
         
         if (rs.next())
         {
            log.info("User exists already");
            retVal = true;
         }
      }
      catch (SQLException e)
      {
         log.error("Failed to check for user '" + username + "': " + e);
      }
      finally
      {
         try
         {
            close(ps);
            close(rs);
         }
         catch (Exception e)
         {
            log.error("Error while closing variables: " + e);
         }
      }
      
      return retVal;
   }
   
   public void changePassword(String newPass)
   {
      PreparedStatement ps = null;
      String sql = "UPDATE users SET password=? WHERE id=? AND username=? LIMIT 1";
      
      try
      {
         if (WorkTimer.getUser().getUserId() != -1)
         {
            ps = getMySQLConn().prepareStatement(sql);
            ps.setString(1, newPass);
            ps.setInt(2, WorkTimer.getUser().getUserId());
            ps.setString(3, WorkTimer.getUser().getUsername());
            ps.execute();
         }
         else
         {
            log.info("User is not logged in...cannot change password");
         }
      }
      catch (Exception e)
      {
         log.error("Failed to change user's password: " + e);
         JOptionPane.showMessageDialog(null,
               "Password change failed.  Please try again.",
               "Error",
               JOptionPane.ERROR_MESSAGE);
      }
      finally
      {
         close(ps);
      }
   }
   
   public void createUser(String username, String password)
   {
      PreparedStatement ps = null;
      String sql = "INSERT INTO users (username, password) VALUES (?, ?)";
      
      try
      {
         ps = getMySQLConn().prepareStatement(sql);
         ps.setString(1, username);
         ps.setString(2, password);
         ps.execute();
      }
      catch (Exception e)
      {
         log.error("Failed to create new user: " + e);
         JOptionPane.showMessageDialog(null,
               "Creation of new user failed.  Please try again.",
               "Error",
               JOptionPane.ERROR_MESSAGE);
      }
      finally
      {
         close(ps);
      }
   }
}
